//
//  DataBase.m
//  UILesson19 - SQLite
//
//  Created by lanou3g on 16/1/8.
//  Copyright © 2016年 徐强. All rights reserved.
//

#import "DataBase.h"

static DataBase *share = nil;
@implementation DataBase
+ (DataBase *)shareDB{
    @synchronized(self) {
        if (share == nil) {
            share = [[DataBase alloc]init];
        }
        return share;
    }
}
//以下是关于数据库的方法
static sqlite3 *db = nil;
#pragma - mark - 打开数据库
- (void)openDB{
    //判断是否已经打开
    if (db == nil) {
        //存储路径
        NSString *filePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"student.sqlite"];
        NSLog(@"%@",filePath);
        //打开数据库
        int result = sqlite3_open(filePath.UTF8String, &db);
        if (result == SQLITE_OK) {
            NSLog(@"打开数据库成功");
            
//            //创建一个表格 学号number作为主键
            NSString * sql = @"CREATE  TABLE  IF NOT EXISTS student (number INTEGER PRIMARY KEY  NOT NULL , name TEXT, age INTEGER)";
            //执行sql语句
            int result1 = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
            if (result1 == SQLITE_OK) {
                NSLog(@"创建表格成功");
            }else{
                NSLog(@"创建表格失败");
            }
            
        }else{
            NSLog(@"打开数据库失败");
        }
    }
}

#pragma - mark - 关闭数据库
- (void)closeDB{
    if (db != nil) {
       int result = sqlite3_close(db);
        if (result == SQLITE_OK) {
            NSLog(@"关闭数据库成功");
            db = nil;
        }else{
            NSLog(@"关闭数据库失败");
        }
    }
}

#pragma amrk -  数据库的基本操作：增、删、改、查
//添加
- (void)addStudent:(Student *)stu{
    //把传入的数据格式化放到sql语句中去
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO student (number,name,age) VALUES ('%ld','%@','%ld')",stu.number,stu.name,stu.age];
    //执行sql语句
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    }else{
        NSLog(@"添加失败");
    }
}
//根据学号删除学生
-(void)deleteStudentWithNumber:(NSInteger)num{
    
    //添加多个条件，用‘，’分开
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM student WHERE NUMBER = '%ld'",num];
    
   int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除数据成功");
    }else{
         NSLog(@"删除数据失败");
    }
}
//修改学生信息
- (void)updateStudent:(Student *)stu{
    
    NSString *sql = [NSString stringWithFormat:@"UPDATE STUDENT SET name = '%@' , age = '%ld' WHERE number = '%ld'",stu.name,stu.age,stu.number];
    
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
    }else{
        NSLog(@"修改失败");
    }
}
//查找所有学生信息
- (NSMutableArray *)selectAllStudent
{
    NSMutableArray *arr = [NSMutableArray array];
    // * 代表所有
    NSString *sql =@"SELECT * FROM student";
    
    sqlite3_stmt *stmt = nil;
    //第三个参数“-1”：自动计算长度
    //第四个参数:游标（🐷）
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"开始查找数据");
        //逐行找，直到找完为止
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //返回number值
           int num = sqlite3_column_int(stmt, 0);
            //得到的是c字符串
           char *name1 = (char *)sqlite3_column_text(stmt, 1);
           NSString *name = [NSString stringWithCString:name1 encoding:NSUTF8StringEncoding];
            int age = sqlite3_column_int(stmt, 2);
            Student *model = [[Student alloc]init];
            model.name = name;
            model.age = age;
            model.number = num;
            [arr addObject:model];
        }
        
    }else{
        NSLog(@"查找失败");
    }
    //释放游标
    sqlite3_finalize(stmt);
    return arr;
}



@end
